package com.dsi.capa.db.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;
import javax.swing.JOptionPane;

import com.dsi.capa.db.model.Alumno;
import com.dsi.capa.db.model.Pago;


public class AlumnoDAO extends BasicDAO<Alumno> {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	

	public AlumnoDAO() {
		super();
	}

	public AlumnoDAO(DataSource ds) {
		super(ds);
	}
	//insertar en la base
	@Override
	public void save(Alumno target) {
		
		StringBuffer sql = new StringBuffer();
		
		//PRIMERO INSERTAR EL ALUMNO
		sql.append("INSERT INTO Alumno(CARNET, NOMBREALUMNO, APELLIDOALUMNO, NITALUMNO, DUIALUMNO)");
		sql.append(" VALUES(?, ?, ?, ?, ?)");
		try {
			preparedStatement = getConnection().prepareStatement(
					sql.toString());
			preparedStatement.setString(1, target.getCarnet());
			preparedStatement.setString(2, target.getNombreAlumno());
			preparedStatement.setString(3, target.getApellidoAlumno());
			preparedStatement.setString(4, target.getNitAlumno());
			preparedStatement.setString(5, target.getDuiAlumno());
			//JOptionPane.showMessageDialog(null, sql.toString());
//			preparedStatement.setString(1, target.getNombres());
//			preparedStatement.setString(2, target.getApellidos());
//			preparedStatement.setString(3, target.getSexo());
			preparedStatement.executeUpdate();
			
			//INSERTAR USUARIO EN OTRA CONSULTA
			sql = new StringBuffer();
			sql.append("INSERT INTO usuario(NOMBREUSUARIO, CONTRASENIA, ROL, EMAIL)");
			sql.append(" VALUES(?, ?, 'alumno', '@correo.com')");
			
			preparedStatement = getConnection().prepareStatement(
					sql.toString());
			preparedStatement.setString(1, target.getCarnet());
			preparedStatement.setString(2, target.getCarnet());
			preparedStatement.executeUpdate();
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}

	@Override
	public void update(Alumno target) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE Alumno SET NOMBREALUMNO = ?, APELLIDOALUMNO = ?, NITALUMNO= ?, ");
		sql.append("DUIALUMNO = ? WHERE CARNET = ? ");
		try {
			preparedStatement = getConnection().prepareStatement(
					sql.toString());
			
			preparedStatement.setString(1, target.getNombreAlumno());
			preparedStatement.setString(2, target.getApellidoAlumno());
			preparedStatement.setString(3, target.getNitAlumno());
			preparedStatement.setString(4, target.getDuiAlumno());
			preparedStatement.setString(5, target.getCarnet());
//			preparedStatement.setString(1, target.getNombres());
//			preparedStatement.setString(2, target.getApellidos());
//			preparedStatement.setString(3, target.getSexo());
//			preparedStatement.setLong(4, target.getCodigo());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		//crearTalonario(target.getCarnet(),"CODIGOCARRERA");
	
	}

	private void crearTalonario(String carnet, String codigoCarrea) {
		Integer i=null;
		
		if(codigoCarrea.equalsIgnoreCase("CODIGOCARRERA"));
			i=6;
		
		//	for(int i=0;i<length)
			
		
	}

	@Override
	public void delete(Alumno target) {
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM Alumno WHERE CARNET = ? ");
		try {
			preparedStatement = getConnection().prepareStatement(
					sql.toString());
			preparedStatement.setString(1, target.getCarnet());
//			preparedStatement.setLong(1, target.getCodigo());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}

	@Override
	public List<Alumno> findAll() {
		return findAll(null, null);
	}

	//@Override
	/*public List<Alumno> findAll(Integer firstRow, Integer maxRows) {
		List<Alumno> Alumno = new ArrayList<Alumno>();
		int i = 0;
		int lastRow = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT CARNET, NOMBREALUMNO, APELLIDOALUMNO, ");
		sql.append("NITALUMNO, DUIALUMNO FROM Alumno");
		
		try {
			preparedStatement = getConnection().prepareStatement(sql.toString());
			resultSet = preparedStatement.executeQuery();
			if(firstRow != null && maxRows != null) {
				lastRow = firstRow.intValue() + maxRows;
				while(resultSet.next()) {
					if(i >= firstRow.intValue()) {
						Alumno c = new Alumno();
						c.setCarnet(resultSet.getString("CARNET"));
						c.setNombreAlumno(resultSet.getString("NOMBREALUMNO"));
						c.setApellidoAlumno(resultSet.getString("APELLIDOALUMNO"));
						c.setNitAlumno(resultSet.getString("NITALUMNO"));
						c.setDuiAlumno(resultSet.getString("DUIALUMNO"));
//						c.setApellidos(resultSet.getString("APELLIDOS_Alumno"));
//						c.setNombres(resultSet.getString("NOMBRES_Alumno"));
//						c.setSexo(resultSet.getString("SEXO_Alumno"));
//						c.setCodigo(resultSet.getLong("CODIGO_Alumno"));
						Alumno.add(c);
					}
					i++;
					if(i >= lastRow) {
						break;
					}
				}
			} else {
				while(resultSet.next()) {
					Alumno c = new Alumno();
					c.setCarnet(resultSet.getString("CARNET"));
					c.setNombreAlumno(resultSet.getString("NOMBREALUMNO"));
					c.setApellidoAlumno(resultSet.getString("APELLIDOALUMNO"));
					c.setNitAlumno(resultSet.getString("NITALUMNO"));
					c.setDuiAlumno(resultSet.getString("DUIALUMNO"));
//					c.setApellidos(resultSet.getString("APELLIDOS_Alumno"));
//					c.setNombres(resultSet.getString("NOMBRES_Alumno"));
//					c.setSexo(resultSet.getString("SEXO_Alumno"));
//					c.setCodigo(resultSet.getLong("CODIGO_Alumno"));
					Alumno.add(c);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		
		return Alumno;
	}*/
	
	/************************* METODO QUE BUSCA UN SOLO ALUMNO *********************************/
	public Alumno buscarAlumno(Alumno target)
	{
		Alumno c = new Alumno();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT CARNET, NOMBREALUMNO, APELLIDOALUMNO, ");
		sql.append("NITALUMNO, DUIALUMNO FROM Alumno WHERE CARNET = ?");
		
		try {
			preparedStatement = getConnection().prepareStatement(sql.toString());
			preparedStatement.setString(1, target.getCarnet());
			resultSet = preparedStatement.executeQuery();
			
				while(resultSet.next()) {
						c.setCarnet(resultSet.getString("CARNET"));
						c.setNombreAlumno(resultSet.getString("NOMBREALUMNO"));
						c.setApellidoAlumno(resultSet.getString("APELLIDOALUMNO"));
						c.setNitAlumno(resultSet.getString("NITALUMNO"));
						c.setDuiAlumno(resultSet.getString("DUIALUMNO"));
					}
				
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		
		return c;

	}//FIN METODO buscarAlumno
	
	/************************* METODO QUE BUSCA UN SOLO ALUMNO *********************************/
	public String correlativo(Alumno target)
	{
		//Alumno c = new Alumno();
		String c=null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT CARNET FROM Alumno WHERE CARNET LIKE ?");
		
		try {
			preparedStatement = getConnection().prepareStatement(sql.toString());
			preparedStatement.setString(1, target.getCarnet() + "%");
			resultSet = preparedStatement.executeQuery();
			
				if(resultSet.last()) {
						c = resultSet.getString("CARNET");
						c = c.substring(4);//OBTENER ULTIMO CORRELATIVO
						//INCREMENTAR CORRELATIVO
						c = String.format("%03d", (Integer.parseInt(c))+1);		
					}				
				//return c;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		
		return c;

	}//FIN METODO buscarAlumno

	/** Busquedas y navegacion **/

	public List<Alumno> findBy(Integer idUsuario, Integer firstRow,
			Integer maxRows) {
		List<Alumno> registros = new ArrayList<Alumno>();
		StringBuffer sql = new StringBuffer();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int i = 1;
		int pos = firstRow.intValue() + maxRows.intValue();
		//sql.append("SELECT CARNET ");
		sql.append("SELECT DESCRIPCION ");
		sql.append("     , CUOTA ");
		sql.append("     , ALUMNO.CARNET ");
		sql.append("     , MONTO ");
		sql.append("     , ESTADOPAGO ");
		sql.append(" FROM FACTURA ");
		sql.append(" JOIN ALUMNO ");
		sql.append("   ON  alumno.carnet=factura.carnet");
		sql.append(" WHERE NOT 1 = 1 ");
	
		if (!isEmpty(idUsuario)) {
			sql.append(" OR alumno.IDUSUARIO LIKE ?");
		}
		/*if (!isEmpty(fecha)) {
			sql.append(" AND PER.NIT_PERSONA LIKE ?");
		}*/

		try {
			connection = ds.getConnection();
			preparedStatement = connection.prepareStatement(sql.toString());
			if (!isEmpty(idUsuario)) {
				preparedStatement.setInt(i++, idUsuario);
			}
//			if (!isEmpty(fecha)) {
//				preparedStatement.setDate(i++,  (java.sql.Date) fecha);
//			}

			resultSet = preparedStatement.executeQuery();
			i = 0;
			while (resultSet.next()) {
				if (i >= firstRow) {
					Alumno cn = getDataRow(resultSet);
					registros.add(cn);
					if (i >= pos) {
						break;
					}
				}
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return registros;
	}

	private Alumno getDataRow(ResultSet resultSet) throws SQLException {
		Alumno cn = new Alumno();
//		cn.setCarnet(resultSet.getString(""));
//		cn.setCuota(resultSet.getDouble(""));
//		cn.setDescripcion(resultSet.getString(""));
//		cn.setFecha(resultSet.getDate(""));
//		cn.setMonto(resultSet.getDouble(""));
		cn.setCarnet(resultSet.getString("CARNET"));
		cn.setDescripcion(resultSet.getString("DESCRIPCION"));
		cn.setCuota(resultSet.getInt("CUOTA"));
		cn.setMonto(resultSet.getDouble("MONTO"));
		cn.setEstadoPago(resultSet.getString("ESTADOPAGO"));

		return cn;
	}

	private boolean isEmpty(Long target) {
		return target == null || target.longValue() == 0;
	}

	private boolean isEmpty(String target) {
		return target == null || target.trim().equals("");
	}

	private boolean isEmpty(Integer target) {
		return target == null || target.intValue() == 0;
	}

	private boolean isEmpty(Date target) {
		return target == null || target.toString().equals("");
	}

	@Override
	public List<Alumno> findAll(Integer firstRow, Integer maxRows) {
		// TODO Auto-generated method stub
		return null;
	}

	/**** ----------------------------------- ***/

	
}
